package com.dhcc.bpm.common.utils;

import cn.hutool.core.util.ReflectUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.fastjson.JSONObject;
import com.dhcc.bpm.modules.system.entity.ExpSeqEntity;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springblade.core.tool.utils.CollectionUtil;
import org.springframework.data.redis.core.StringRedisTemplate;
import org.springframework.data.redis.core.ValueOperations;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.io.File;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
/*
 * @Description 将表数据导出为新增sql
 * @Author xiafayu
 * @Date 2020/12/23 11:35
 **/
@Component
public class CreateInsert {
    private static final Logger log = LoggerFactory.getLogger(CreateInsert.class);
    @Resource
    private JdbcTemplate jdbcTemplate;
    @Resource
    private StringRedisTemplate stringRedisTemplate;
    private static final String EXP_SQE = "expSeq";
    private static Pattern linePattern = Pattern.compile("_(\\w)");

    /**
     * 生成插入语句
     * @param tablename 表明
     * @param clazz 与数据库中字段一一对应的类
     * @param t 有数据的实体
     * @param <T> 数据实体类型 如 User
     */
    public static  <T> String getInsertSql(String tablename, Class<T> clazz, T t){
        String sql = "";
        Field[] fields = ReflectUtil.getFieldsDirectly(clazz, false);
        StringBuffer topHalf = new StringBuffer("insert into "+tablename+" (");
        StringBuffer afterAalf = new StringBuffer("values (");
        for (Field field : fields) {
            topHalf.append(field.getName() + ",");
            if (ReflectUtil.getFieldValue(t, field.getName()) instanceof String) {
                afterAalf.append("'" + ReflectUtil.getFieldValue(t, field.getName()) + "',");
            } else {
                afterAalf.append(ReflectUtil.getFieldValue(t, field.getName()) + ",");
            }
        }
        topHalf = new StringBuffer(StrUtil.removeSuffix(topHalf.toString(), ","));
        afterAalf = new StringBuffer(StrUtil.removeSuffix(afterAalf.toString(), ","));
        topHalf.append(") ");
        afterAalf.append(") ");
        sql = topHalf.toString() + afterAalf.toString();
        return sql;
    }

    /**
     * 生成更新语句
     * 必须含有id
     * 数据实体中 null 与 空字段不参与更新
     * @param tablename 数据库中的表明
     * @param clazz 与数据库中字段一一对应的类
     * @param t 有数据的实体
     * @param <T> 数据实体类型,如 User
     */
    public static  <T> String getUpdateSql(String tablename, Class<T> clazz, T t){
        String sql = "";
        String id = ""; //保存id名：ID or id
        Field[] fields = ReflectUtil.getFieldsDirectly(clazz, false);
        sql = "update "+tablename+" set ";
        for (Field field : fields) {
            StringBuffer tmp = new StringBuffer();
            if ("ID".equals(field.getName()) || "id".equals(field.getName())){
                id = field.getName();
                continue;//更新的时候无需set id=xxx
            }
            if (ReflectUtil.getFieldValue(t, field.getName()) != null && ReflectUtil.getFieldValue(t, field.getName()) != "") {
                tmp.append( field.getName() + "=");
                if (ReflectUtil.getFieldValue(t, field.getName()) instanceof String) {
                    tmp.append( "'" + ReflectUtil.getFieldValue(t, field.getName()) + "',");
                } else {
                    tmp.append(ReflectUtil.getFieldValue(t, field.getName()) + ",");
                }
                sql += tmp;
            }
        }
        sql = StrUtil.removeSuffix(sql, ",") + " where " + id + "='" + ReflectUtil.getFieldValue(t, id)+"'";
        return sql;
    }


    /**
     * //根据数据库生成字段 例如 private Object a;
     * @param tableName 表名称
     * @return 成员变量拼接后的字符串
     * @throws SQLException
     */
    public <T> String getInsertSql(String tableName, String expSeq) {
        StringBuilder sql = null;
        try(Connection connection = jdbcTemplate.getDataSource().getConnection();){
            DatabaseMetaData data = connection.getMetaData();
            String driverName = data.getDriverName();
            StringBuffer topHalf = new StringBuffer("INSERT INTO " + tableName.toUpperCase() + " (");
            List<TableDataMapper.TableData> rs = null;
            if(driverName.contains("Oracle")){
                //oracle数据库
                rs = jdbcTemplate.query("select DISTINCT COLUMN_NAME,DATA_TYPE from USER_TAB_COLUMNS where table_name='" + tableName.toUpperCase() + "'", new TableDataMapper());

                for (TableDataMapper.TableData tableData : rs){
                    if("DESCRIBE".equals(tableData.getColumn_name().toUpperCase())){
                        topHalf.append('"' + tableData.getColumn_name()+ '"' + ",");
                    }else{
                        topHalf.append(tableData.getColumn_name()+ ",");
                    }
                }
            } else{
                //mysql数据库
                String url = data.getURL();
                String[] split = url.split(":");
                String[] portSplit = split[3].split("/");
                String[] databaseSplit = portSplit[1].split("\\?");
                String dbName = databaseSplit[0];

                rs = jdbcTemplate.query("select column_name,data_type  from information_schema.columns where table_schema='" + dbName + "' and table_name='" + tableName.toLowerCase() + "'", new TableDataMapper());

                for (TableDataMapper.TableData tableData : rs){
                    topHalf.append( "`" + tableData.getColumn_name()+ "`,");
                }
            }

            topHalf = new StringBuffer(StrUtil.removeSuffix(topHalf.toString(), ","));
            topHalf.append(") ");
            log.info("字段：{}", topHalf);


            List<Map<String,Object>> rows = jdbcTemplate.queryForList("SELECT * FROM " + tableName + " where exp_seq = '" + expSeq + "'");
            log.info(tableName + "表需要导出的数据为：{}", rows);
            sql = new StringBuilder();
            for(Map<String,Object> map: rows){
                StringBuilder column = new StringBuilder("(");
                for (TableDataMapper.TableData tableData : rs){
                    if(map.get(tableData.getColumn_name()) != null){
                        if("DATE".equals(tableData.getData_type().toUpperCase())){
                            column.append("TIMESTAMP'" + map.get(tableData.getColumn_name()) + "',");
                        }else if("NCLOB".equals(tableData.getData_type().toUpperCase())){
                            column.append("TO_CLOB('" + map.get(tableData.getColumn_name()) + "'),");
                        }else if("NUMBER".equals(tableData.getData_type().toUpperCase())){
                            column.append(map.get(tableData.getColumn_name()) + ",");
                        }else{
                            column.append("'" + map.get(tableData.getColumn_name()) + "',");
                        }
                    }else{
                        column.append(null + ",");
                    }
                }

                column.replace(column.length() - 1, column.length(), ")");

                sql.append(topHalf.toString() + " VALUES " + column.toString() + ";");
                sql.append("\r\n");
            }

        } catch (SQLException e) {
            log.error("异常信息：{}", e);
        }

        log.info(tableName + "生成的新增sql:{}", sql);
        return sql.toString();
    }


    /**
     * //根据数据库生成字段 例如 private Object a;
     * @param tableName 表名称
     * @param productIds 产品名称
     * @param downloadNumType 导出类型 0-全部 1-非门户
     * @return 成员变量拼接后的字符串
     * @throws SQLException
     */
    public <T> JSONObject getInsertSqlbySysIds(String tableName, String sysIds, String productIds,String downloadNumType) {
        StringBuilder sql = null;
        JSONObject jsonObject = new JSONObject();
        try(Connection connection = jdbcTemplate.getDataSource().getConnection();){
            DatabaseMetaData data = connection.getMetaData();
            String driverName = data.getDriverName();
            StringBuffer topHalf = new StringBuffer("INSERT INTO " + tableName.toUpperCase() + " (");
            List<TableDataMapper.TableData> rs = null;
            if(driverName.contains("Oracle")){
                //oracle数据库
                rs = jdbcTemplate.query("select DISTINCT COLUMN_NAME,DATA_TYPE from USER_TAB_COLUMNS where table_name='" + tableName.toUpperCase() + "'", new TableDataMapper());

                for (TableDataMapper.TableData tableData : rs){
                    if("DESCRIBE".equals(tableData.getColumn_name().toUpperCase())){
                        topHalf.append('"' + tableData.getColumn_name()+ '"' + ",");
                    }else{
                        topHalf.append(tableData.getColumn_name()+ ",");
                    }
                }
            } else{
                //mysql数据库
                String url = data.getURL();
                String[] split = url.split(":");
                String[] portSplit = split[3].split("/");
                String[] databaseSplit = portSplit[1].split("\\?");
                String dbName = databaseSplit[0];

                rs = jdbcTemplate.query("select column_name,data_type  from information_schema.columns where table_schema='" + dbName + "' and table_name='" + tableName.toLowerCase() + "'", new TableDataMapper());

                for (TableDataMapper.TableData tableData : rs){
                    topHalf.append( "`" + tableData.getColumn_name()+ "`,");
                }
            }

            topHalf = new StringBuffer(StrUtil.removeSuffix(topHalf.toString(), ","));
            topHalf.append(") ");
            log.info("字段：{}", topHalf);
            List<Map<String,Object>> rows = new ArrayList<>();
            List<Map<String,Object>> FileMap = new ArrayList<>();
            ArrayList fileList = new ArrayList();
            switch (tableName) {
                case "opc_exp_seq":
//                    rows = jdbcTemplate.queryForList("SELECT * FROM " + tableName + " where EXP_SEQ in ( select EXP_SEQ  from opc_base_product  where  product_ename in ( " + productIds + " ) ) for update");
                    break;
                case "opc_sys":
                    rows = jdbcTemplate.queryForList("SELECT * FROM " + tableName + " where id in ( " + sysIds + ") for update");
                    break;
                case "opc_base_product":
                    rows = jdbcTemplate.queryForList("SELECT * FROM " + tableName + " where id in ( " + productIds + " ) for update");
                    break;
                case "opc_backservice":
                    rows = jdbcTemplate.queryForList("SELECT * FROM " + tableName + " where id in (select atomic_service_id from opc_api  where backservice_id in ( " + productIds + " )  ) for update");
                    break;
                case "opc_api":
                    rows = jdbcTemplate.queryForList("SELECT * FROM " + tableName + " where atomic_service_id in( select  id  from opc_backservice where id in ( select atomic_service_id from opc_api  where backservice_id in( " + productIds + " ) ) ) for update");
                    if ("1".equals(downloadNumType)) {
                        for (Map<String, Object> map : rows) {
                            map.put("STATUS", 9);
                        }
                    }
                    break;
                case "opc_gateway_route":
                    rows = jdbcTemplate.queryForList("SELECT * FROM " + tableName + " a where a.back_service_id in (" + productIds + " ) for update");
                    break;
                case "opc_product":
                    rows.addAll(jdbcTemplate.queryForList("SELECT * FROM " + tableName + " where id in (SELECT product_id FROM opc_product_api where api_id in (select id  from opc_api  where atomic_service_id in(select id from opc_backservice where id in (select atomic_service_id from opc_api where backservice_id in(select id from opc_base_product  where  product_ename in (" + productIds + " ) ) ) ) )) or product_ename in (" + productIds + " ) for update"));
                    if ("1".equals(downloadNumType)) {
                        for (Map<String, Object> map : rows) {
                            map.put("STATUS", 9);
                        }
                    }
                    break;
                case "opc_product_api":
                    rows = jdbcTemplate.queryForList("SELECT * FROM " + tableName + " where api_id in (select id  from opc_api  where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + " )  ) ) ) for update");
                    if ("1".equals(downloadNumType)) {
                        for (Map<String, Object> map : rows) {
                            map.put("STATUS", 9);
                        }
                    }
                    break;
                case "opc_app":
                    if ("0".equals(downloadNumType)) {
                        rows = jdbcTemplate.queryForList("SELECT * FROM " + tableName + " where id in (select opc_app_id from opc_app_api where opc_api_id in(select id from opc_api where backservice_id in (" + productIds + " ) ) ) for update");
                    }
                    break;
                case "opc_app_api":
                    if ("0".equals(downloadNumType)) {
                        rows = jdbcTemplate.queryForList("SELECT * FROM " + tableName + " where opc_api_id in (select id from opc_api where backservice_id in(" + productIds + " )) for update");
                    }
                    break;
                case "OPC_ISSUE_API":
                    if ("0".equals(downloadNumType)) {
                        rows = jdbcTemplate.queryForList("SELECT * FROM " + tableName + " where api_id in (select id from opc_api  where atomic_service_id in(select  id  from opc_backservice where id in (select atomic_service_id from opc_api  where backservice_id in(" + productIds + " ) ) ) ) for update");
                    }
                    break;
                case "OPC_EXTERNALCALL":
                    rows = jdbcTemplate.queryForList("SELECT * FROM " + tableName + " where backservice_id in (select id from opc_backservice where id in (select atomic_service_id from opc_api where backservice_id in(" + productIds + " ) ) ) for update");
                    break;
                case "opc_test_apply":
                    if ("0".equals(downloadNumType)) {
                        rows = jdbcTemplate.queryForList("SELECT * FROM " + tableName + " where app_id in(select id from opc_app where id in (select opc_app_id from opc_app_api where opc_api_id in(select id from opc_api where backservice_id in (" + productIds + " ) ) ) ) for update");
                    }
                    break;
                case "opc_produce_apply":
                    if ("0".equals(downloadNumType)) {

                        rows = jdbcTemplate.queryForList("SELECT * FROM " + tableName + " where app_id in (select id from opc_app where id in (select opc_app_id from opc_app_api where opc_api_id in(select id from opc_api where backservice_id in (" + productIds + " ) ) ) ) for update");
                    }
                    break;
                case "opc_online_apply":
                    if ("0".equals(downloadNumType)) {

                        rows = jdbcTemplate.queryForList("SELECT * FROM " + tableName + " where app_id in(select id from opc_app where id in (select opc_app_id from opc_app_api where opc_api_id in (select id from opc_api where backservice_id in (" + productIds + " ) ) ) ) for update");
                    }
                    break;
                case "opc_orga":
                    if ("0".equals(downloadNumType)) {

                        rows = jdbcTemplate.queryForList("SELECT * FROM " + tableName + " where id in ( SELECT opc_orga_id FROM opc_app where id in (select opc_app_id from opc_app_api where opc_api_id in (select id from opc_api where backservice_id in (" + productIds + " ) ) ) ) for update");
                    }
                    break;
                case "opc_user":
                    if ("0".equals(downloadNumType)) {
                        rows = jdbcTemplate.queryForList("SELECT * FROM " + tableName + " where id in ( select opc_user_id from opc_orga where id in ( SELECT opc_orga_id FROM opc_app where id in (select opc_app_id from opc_app_api where opc_api_id in(select id from opc_api where backservice_id in (" + productIds + " ) ) ) ) ) for update");
                    }
                    break;
                case "opc_plupload":
                    List<Map<String,Object>> ids = jdbcTemplate.queryForList("select file_ids from opc_api where backservice_id in(" + productIds + " )");
                    String fileIds = "";
                    for (int j = 0; j < ids.size(); j++) {
                        Map<String, Object> map = ids.get(j);
                        if(StringUtils.isNotEmpty((String) map.get("FILE_IDS"))){
                            String[] file_ids = ((String) map.get("FILE_IDS")).split(",");
                            if (file_ids.length > 0) {
                                for (int i = 0; i < file_ids.length; i++) {
                                    if (i == (file_ids.length - 1)) {
                                        fileIds = fileIds + "'" + file_ids[i] + "'";
                                    } else {
                                        fileIds = fileIds + "'" + file_ids[i] + "',";
                                    }
                                }
                            } else {
                                fileIds = fileIds + "'" + (String) map.get("FILE_IDS") + "'";
                            }
                            if (j != (ids.size() - 1)){
                                if (fileIds.lastIndexOf(',') != fileIds.length()){
                                    fileIds = fileIds + ",";
                                }
                            }
                        }
                    }
                    if (fileIds == ",") {
                        fileIds = "";
                    }
                    if (fileIds != "") {
                        if (fileIds.lastIndexOf(',') == fileIds.length() - 1) {
                            fileIds = fileIds.substring(0, fileIds.length() - 1);
                        }
                    }else {
                        fileIds = "''";
                    }
                    if(driverName.contains("Oracle")){
                        rows = jdbcTemplate.queryForList("SELECT * FROM " + tableName + " where id in ( select msg_config_id || '' from opc_api where atomic_service_id in(select  id  from opc_backservice where id in (select atomic_service_id from opc_api  where backservice_id in(" + productIds + " ))) and msg_config_id != '-1' union select bus_id || '' from opc_api where atomic_service_id in(select  id  from opc_backservice where id in (select atomic_service_id from opc_api  where backservice_id in(" + productIds + " ))) and bus_id != '-1' union  select xml_msg_id || '' from opc_api where atomic_service_id in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + " ))) and xml_msg_id != '-1' union  select json_msg_config || '' from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + " ))) and json_msg_config != '-1' union  select sep_msg_config || '' from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + " ))) and sep_msg_config != '-1' union  select fixed_msg_config || '' from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + " ))) and fixed_msg_config != '-1'  union  select kv_msg_config || '' from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + " ))) and kv_msg_config != '-1' union  select xml_msg_config || '' from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + " ))) and xml_msg_config != '-1' union  select efet_msg_config || '' from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + " ))) and efet_msg_config != '-1' union  select is_upload_back_file || '' from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + " ))) and is_upload_back_file != '-1' ) union SELECT * FROM " + tableName + " where id in (select file_path from opc_base_product  where  id in ( " + productIds + "  )) union SELECT * FROM " + tableName + " where id in ( select file_path from opc_product  where  id in( SELECT PRODUCT_ID FROM opc_product_api WHERE api_id IN (SELECT id FROM opc_api WHERE BACKSERVICE_ID IN (" + productIds + "  ))))");
                        FileMap = jdbcTemplate.queryForList("SELECT file_fullpath FROM " + tableName + " where id in ( select msg_config_id || '' from opc_api where atomic_service_id in(select  id  from opc_backservice where id in (select atomic_service_id from opc_api  where backservice_id in(" + productIds + " ))) and msg_config_id != '-1' union select bus_id || '' from opc_api where atomic_service_id in(select  id  from opc_backservice where id in (select atomic_service_id from opc_api  where backservice_id in(" + productIds + " ))) and bus_id != '-1' union  select xml_msg_id || '' from opc_api where atomic_service_id in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + " ))) and xml_msg_id != '-1' union  select json_msg_config || '' from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + " ))) and json_msg_config != '-1' union  select sep_msg_config || '' from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + " ))) and sep_msg_config != '-1' union  select fixed_msg_config || '' from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + " ))) and fixed_msg_config != '-1'  union  select kv_msg_config || '' from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + " ))) and kv_msg_config != '-1' union  select xml_msg_config || '' from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + " ))) and xml_msg_config != '-1' union  select efet_msg_config || '' from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + " ))) and efet_msg_config != '-1' union  select is_upload_back_file || '' from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + " ))) and is_upload_back_file != '-1' ) union SELECT FILE_FULLPATH FROM " + tableName + " where id in (select file_path from opc_base_product  where  id in ( " + productIds + "  )) union SELECT FILE_FULLPATH FROM " + tableName + " where id in ( select file_path from opc_product  where  id in( SELECT PRODUCT_ID FROM opc_product_api WHERE api_id IN (SELECT id FROM opc_api WHERE BACKSERVICE_ID IN (" + productIds + "  ))))");
                    }else {
                        rows = jdbcTemplate.queryForList("SELECT * FROM " + tableName + " where id in ( select concat(msg_config_id,'') from opc_api where atomic_service_id in(select  id  from opc_backservice where id in (select atomic_service_id from opc_api  where backservice_id in(" + productIds + " ))) and msg_config_id != '-1' union select concat(bus_id,'') from opc_api where atomic_service_id in(select  id  from opc_backservice where id in (select atomic_service_id from opc_api  where backservice_id in(" + productIds + " ))) and bus_id != '-1' union  select concat(xml_msg_id,'') from opc_api where atomic_service_id in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + " ))) and xml_msg_id != '-1' union  select concat(json_msg_config,'') from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + " ))) and json_msg_config != '-1' union  select concat(sep_msg_config,'') from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + " ))) and sep_msg_config != '-1' union  select concat(fixed_msg_config,'') from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + " ))) and fixed_msg_config != '-1'  union  select concat(kv_msg_config,'') from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + " )))) and kv_msg_config != '-1' union  select concat(xml_msg_config,'') from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + " ))) and xml_msg_config != '-1' union  select concat(efet_msg_config,'') from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + " ))) and efet_msg_config != '-1' union  select concat(is_upload_back_file,'') from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + " ))) and is_upload_back_file != '-1' ) union SELECT * FROM " + tableName + " where id in (select file_path from opc_base_product  where  id in ( " + productIds + "  )) union SELECT * FROM " + tableName + " where id in ( select file_path from opc_product  where  id in( SELECT PRODUCT_ID FROM opc_product_api WHERE api_id IN (SELECT id FROM opc_api WHERE BACKSERVICE_ID IN (" + productIds + "  ))))");
                        FileMap = jdbcTemplate.queryForList("SELECT file_fullpath FROM " + tableName + " where id in ( select concat(msg_config_id,'') from opc_api where atomic_service_id in(select  id  from opc_backservice where id in (select atomic_service_id from opc_api  where backservice_id in(" + productIds + " ))) and msg_config_id != '-1' union select concat(bus_id,'') from opc_api where atomic_service_id in(select  id  from opc_backservice where id in (select atomic_service_id from opc_api  where backservice_id in(" + productIds + " ))) and bus_id != '-1' union  select concat(xml_msg_id,'') from opc_api where atomic_service_id in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + " ))) and xml_msg_id != '-1' union  select concat(json_msg_config,'') from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + " ))) and json_msg_config != '-1' union  select concat(sep_msg_config,'') from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + " ))) and sep_msg_config != '-1' union  select concat(fixed_msg_config,'') from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + " ))) and fixed_msg_config != '-1'  union  select concat(kv_msg_config,'') from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + " ))) and kv_msg_config != '-1' union  select concat(xml_msg_config,'') from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + " ))) and xml_msg_config != '-1' union  select concat(efet_msg_config,'') from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + " ))) and efet_msg_config != '-1' union  select concat(is_upload_back_file,'') from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + " ))) and is_upload_back_file != '-1' ) union SELECT FILE_FULLPATH FROM " + tableName + " where id in (select file_path from opc_base_product  where  id in ( " + productIds + "  )) union SELECT FILE_FULLPATH FROM " + tableName + " where id in ( select file_path from opc_product  where  id in( SELECT PRODUCT_ID FROM opc_product_api WHERE api_id IN (SELECT id FROM opc_api WHERE BACKSERVICE_ID IN (" + productIds + "  ))))");
                    }
                    rows.addAll(jdbcTemplate.queryForList("SELECT * FROM " + tableName + " where id in ( "+ fileIds +" )"));
                    FileMap.addAll(jdbcTemplate.queryForList("SELECT file_fullpath FROM " + tableName + " where id in ( "+ fileIds +" )"));


                    for(Map<String, Object> dowFile : FileMap){
                        String file_fullpath = (String)dowFile.get("FILE_FULLPATH");
                        fileList.add(new File(file_fullpath));
                    }

                    break;
                case "opc_policy_item_metadata":
                    rows = jdbcTemplate.queryForList("SELECT * FROM " + tableName + " where API_ID in ( select id from opc_api where atomic_service_id in(select  id  from opc_backservice where id in (select atomic_service_id from opc_api  where backservice_id in(" + productIds + ")))) for update");
                    break;
                case "opc_policy":
                    rows = jdbcTemplate.queryForList("SELECT * FROM " + tableName + " where API_ID in ( select id from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where backservice_id in(" + productIds + ")))) for update");
                    FileMap = jdbcTemplate.queryForList("SELECT FILE_NAME, FILE_URL FROM " + tableName + " where API_ID in ( select id from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where backservice_id in(" + productIds + ")))) for update");

                    for(Map<String, Object> dowFile : FileMap){
                        String file_fullpath = (String)dowFile.get("FILE_URL") + (String)dowFile.get("FILE_NAME");
                        fileList.add(new File(file_fullpath));
                    }
                    break;
                case "opc_policy_tag":
                    rows = jdbcTemplate.queryForList("SELECT * FROM " + tableName + " where policy_id in ( select id from opc_policy where API_ID in ( select id from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where backservice_id in(" + productIds + "))))) for update");
                    break;
                case "OPC_POLICY_ITEM":
                    rows = jdbcTemplate.queryForList("SELECT * FROM " + tableName + " where policy_tag_id in ( select id from opc_policy_tag where policy_id in ( select id from opc_policy where API_ID in ( select id from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where backservice_id in(" + productIds + ")))))) for update");
                    break;
                default:
                    break;
            }
            jsonObject.put("fileList", fileList);
            log.info(tableName + "表需要导出的数据为：{}", rows);
            sql = new StringBuilder();
            for(Map<String,Object> map: rows){
                StringBuilder column = new StringBuilder("(");
                for (TableDataMapper.TableData tableData : rs){
                    if(map.get(tableData.getColumn_name()) != null){
                        if("DATE".equals(tableData.getData_type().toUpperCase())){
                            column.append("TIMESTAMP'" + map.get(tableData.getColumn_name()) + "',");
                        }else if("NCLOB".equals(tableData.getData_type().toUpperCase())){
                            column.append("TO_CLOB('" + map.get(tableData.getColumn_name()) + "'),");
                        }else if("NUMBER".equals(tableData.getData_type().toUpperCase())){
                            column.append(map.get(tableData.getColumn_name()) + ",");
                        }else{
                            column.append("'" + map.get(tableData.getColumn_name()) + "',");
                        }
                    }else{
                        column.append(null + ",");
                    }
                }

                column.replace(column.length() - 1, column.length(), ")");

                sql.append(topHalf.toString() + " VALUES " + column.toString() + ";");
                sql.append("\r\n");
            }

        } catch (SQLException e) {
            log.error("异常信息：{}", e);
        }

        log.info(tableName + "生成的新增sql:{}", sql);
        jsonObject.put("sql", sql.toString());
        return jsonObject;
    }

    /**
     * //根据数据库生成字段 例如 private Object a;
     * @param tableName 表名称
     * @param productIds 产品名称
     * @param downloadNumType 导出类型 0-全部 1-非门户
     * @return 成员变量拼接后的字符串
     * @throws SQLException
     */
    public <T> JSONObject getInsertSqlbyProductIds(String tableName, String productIds,String downloadNumType) {
        StringBuilder sql = null;
        JSONObject jsonObject = new JSONObject();
        try(Connection connection = jdbcTemplate.getDataSource().getConnection();){
            DatabaseMetaData data = connection.getMetaData();
            String driverName = data.getDriverName();
            StringBuffer topHalf = new StringBuffer("INSERT INTO " + tableName.toUpperCase() + " (");
            List<TableDataMapper.TableData> rs = null;
            if(driverName.contains("Oracle")){
                //oracle数据库
                rs = jdbcTemplate.query("select DISTINCT COLUMN_NAME,DATA_TYPE from USER_TAB_COLUMNS where table_name='" + tableName.toUpperCase() + "'", new TableDataMapper());

                for (TableDataMapper.TableData tableData : rs){
                    if("DESCRIBE".equals(tableData.getColumn_name().toUpperCase())){
                        topHalf.append('"' + tableData.getColumn_name()+ '"' + ",");
                    }else{
                        topHalf.append(tableData.getColumn_name()+ ",");
                    }
                }
            } else{
                //mysql数据库
                String url = data.getURL();
                String[] split = url.split(":");
                String[] portSplit = split[3].split("/");
                String[] databaseSplit = portSplit[1].split("\\?");
                String dbName = databaseSplit[0];

                rs = jdbcTemplate.query("select column_name,data_type  from information_schema.columns where table_schema='" + dbName + "' and table_name='" + tableName.toLowerCase() + "'", new TableDataMapper());

                for (TableDataMapper.TableData tableData : rs){
                    topHalf.append( "`" + tableData.getColumn_name()+ "`,");
                }
            }

            topHalf = new StringBuffer(StrUtil.removeSuffix(topHalf.toString(), ","));
            topHalf.append(") ");
            log.info("字段：{}", topHalf);
            List<Map<String,Object>> rows = new ArrayList<>();
            List<Map<String,Object>> FileMap = new ArrayList<>();
            ArrayList fileList = new ArrayList();
            switch (tableName) {
                case "opc_exp_seq":
//                    rows = jdbcTemplate.queryForList("SELECT * FROM " + tableName + " where EXP_SEQ in ( select EXP_SEQ  from opc_base_product  where  product_ename in ( " + productIds + " ) ) for update");
                    break;
                case "opc_sys":
                    rows = jdbcTemplate.queryForList("SELECT * FROM " + tableName + " where id in ( select opc_sys_id from opc_base_product  where id in(" + productIds + ") ) for update");
                    break;
                case "opc_base_product":
                    rows = jdbcTemplate.queryForList("SELECT * FROM " + tableName + " where id in ( " + productIds + " ) for update");
                    break;
                case "opc_backservice":
                    rows = jdbcTemplate.queryForList("SELECT * FROM " + tableName + " where id in (select atomic_service_id from opc_api  where backservice_id in ( " + productIds + " )  ) for update");
                    break;
                case "opc_api":
                    rows = jdbcTemplate.queryForList("SELECT * FROM " + tableName + " where atomic_service_id in( select  id  from opc_backservice where id in ( select atomic_service_id from opc_api  where backservice_id in( " + productIds + " ) ) ) for update");
                    if ("1".equals(downloadNumType)) {
                        for (Map<String, Object> map : rows) {
                            map.put("STATUS", 9);
                        }
                    }
                    break;
                case "opc_gateway_route":
                    rows = jdbcTemplate.queryForList("SELECT * FROM " + tableName + " a where a.back_service_id in (" + productIds + " ) for update");
                    break;
                case "opc_product":
                    rows.addAll(jdbcTemplate.queryForList("SELECT * FROM " + tableName + " where id in (SELECT product_id FROM opc_product_api where api_id in (select id  from opc_api  where atomic_service_id in(select id from opc_backservice where id in (select atomic_service_id from opc_api where backservice_id in(select id from opc_base_product  where  product_ename in (" + productIds + " ) ) ) ) )) or product_ename in (" + productIds + " ) for update"));
                    if ("1".equals(downloadNumType)) {
                        for (Map<String, Object> map : rows) {
                            map.put("STATUS", 9);
                        }
                    }
                    break;
                case "opc_product_api":
                    rows = jdbcTemplate.queryForList("SELECT * FROM " + tableName + " where api_id in (select id  from opc_api  where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + " )  ) ) ) for update");
                    if ("1".equals(downloadNumType)) {
                        for (Map<String, Object> map : rows) {
                            map.put("STATUS", 9);
                        }
                    }
                    break;
                case "opc_app":
                    if ("0".equals(downloadNumType)) {
                        rows = jdbcTemplate.queryForList("SELECT * FROM " + tableName + " where id in (select opc_app_id from opc_app_api where opc_api_id in(select id from opc_api where backservice_id in (" + productIds + " ) ) ) for update");
                    }
                    break;
                case "opc_app_api":
                    if ("0".equals(downloadNumType)) {
                        rows = jdbcTemplate.queryForList("SELECT * FROM " + tableName + " where opc_api_id in (select id from opc_api where backservice_id in(" + productIds + " )) for update");
                    }
                    break;
                case "OPC_ISSUE_API":
                    if ("0".equals(downloadNumType)) {
                        rows = jdbcTemplate.queryForList("SELECT * FROM " + tableName + " where api_id in (select id from opc_api  where atomic_service_id in(select  id  from opc_backservice where id in (select atomic_service_id from opc_api  where backservice_id in(" + productIds + " ) ) ) ) for update");
                    }
                    break;
                case "OPC_EXTERNALCALL":
                    rows = jdbcTemplate.queryForList("SELECT * FROM " + tableName + " where backservice_id in (select id from opc_backservice where id in (select atomic_service_id from opc_api where backservice_id in(" + productIds + " ) ) ) for update");
                    break;
                case "opc_test_apply":
                    if ("0".equals(downloadNumType)) {
                        rows = jdbcTemplate.queryForList("SELECT * FROM " + tableName + " where app_id in(select id from opc_app where id in (select opc_app_id from opc_app_api where opc_api_id in(select id from opc_api where backservice_id in (" + productIds + " ) ) ) ) for update");
                    }
                    break;
                case "opc_produce_apply":
                    if ("0".equals(downloadNumType)) {

                        rows = jdbcTemplate.queryForList("SELECT * FROM " + tableName + " where app_id in (select id from opc_app where id in (select opc_app_id from opc_app_api where opc_api_id in(select id from opc_api where backservice_id in (" + productIds + " ) ) ) ) for update");
                    }
                    break;
                case "opc_online_apply":
                    if ("0".equals(downloadNumType)) {

                        rows = jdbcTemplate.queryForList("SELECT * FROM " + tableName + " where app_id in(select id from opc_app where id in (select opc_app_id from opc_app_api where opc_api_id in (select id from opc_api where backservice_id in (" + productIds + " ) ) ) ) for update");
                    }
                    break;
                case "opc_orga":
                    if ("0".equals(downloadNumType)) {

                        rows = jdbcTemplate.queryForList("SELECT * FROM " + tableName + " where id in ( SELECT opc_orga_id FROM opc_app where id in (select opc_app_id from opc_app_api where opc_api_id in (select id from opc_api where backservice_id in (" + productIds + " ) ) ) ) for update");
                    }
                    break;
                case "opc_user":
                    if ("0".equals(downloadNumType)) {
                        rows = jdbcTemplate.queryForList("SELECT * FROM " + tableName + " where id in ( select opc_user_id from opc_orga where id in ( SELECT opc_orga_id FROM opc_app where id in (select opc_app_id from opc_app_api where opc_api_id in(select id from opc_api where backservice_id in (" + productIds + " ) ) ) ) ) for update");
                    }
                    break;
                case "opc_plupload":
                    List<Map<String,Object>> ids = jdbcTemplate.queryForList("select file_ids from opc_api where backservice_id in(" + productIds + " )");
                    String fileIds = "";
                    for (int j = 0; j < ids.size(); j++) {
                        Map<String, Object> map = ids.get(j);
                        if(StringUtils.isNotEmpty((String) map.get("FILE_IDS"))){
                            String[] file_ids = ((String) map.get("FILE_IDS")).split(",");
                            if (file_ids.length > 0) {
                                for (int i = 0; i < file_ids.length; i++) {
                                    if (i == (file_ids.length - 1)) {
                                        fileIds = fileIds + "'" + file_ids[i] + "'";
                                    } else {
                                        fileIds = fileIds + "'" + file_ids[i] + "',";
                                    }
                                }
                            } else {
                                fileIds = fileIds + "'" + (String) map.get("FILE_IDS") + "'";
                            }
                            if (j != (ids.size() - 1)){
                                if (fileIds.lastIndexOf(',') != fileIds.length()){
                                    fileIds = fileIds + ",";
                                }
                            }
                        }
                    }
                    if (fileIds == ",") {
                        fileIds = "";
                    }
                    if (fileIds != "") {
                        if (fileIds.lastIndexOf(',') == fileIds.length() - 1) {
                            fileIds = fileIds.substring(0, fileIds.length() - 1);
                        }
                    }else {
                        fileIds = "''";
                    }
                    if(driverName.contains("Oracle")){
                        rows = jdbcTemplate.queryForList("SELECT * FROM " + tableName + " where id in ( select msg_config_id || '' from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + "))) and msg_config_id != '-1' union select bus_id || '' from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + " ))) and bus_id != '-1' union  select xml_msg_id || '' from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + " ))) and xml_msg_id != '-1' union  select json_msg_config || '' from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + " ))) and json_msg_config != '-1' union  select sep_msg_config || '' from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + " ))) and sep_msg_config != '-1' union  select fixed_msg_config || '' from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + " ))) and fixed_msg_config != '-1'  union  select kv_msg_config || '' from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + " ))) and kv_msg_config != '-1' union  select xml_msg_config || '' from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + " ))) and xml_msg_config != '-1' union  select efet_msg_config || '' from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + " ))) and efet_msg_config != '-1' union  select is_upload_back_file || '' from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + " ))) and is_upload_back_file != '-1' ) union SELECT * FROM " + tableName + " where id in (select file_path from opc_base_product  where  id in ( " + productIds + "  )) union SELECT * FROM " + tableName + " where id in ( select file_path from opc_product  where id in( SELECT product_id FROM opc_product_api WHERE api_id IN (SELECT id FROM opc_api WHERE backservice_id IN ( " + productIds + "  ))))");
                        FileMap = jdbcTemplate.queryForList("SELECT FILE_FULLPATH FROM " + tableName + " where id in ( select msg_config_id || '' from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + " ))) and msg_config_id != '-1' union select bus_id || '' from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + " ))) and bus_id != '-1' union  select xml_msg_id || '' from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + " ))) and xml_msg_id != '-1' union  select json_msg_config || '' from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + " ))) and json_msg_config != '-1' union  select sep_msg_config || '' from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + " ))) and sep_msg_config != '-1' union  select fixed_msg_config || '' from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + " ))) and fixed_msg_config != '-1'  union  select kv_msg_config || '' from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + " ))) and kv_msg_config != '-1' union  select xml_msg_config || '' from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + " ))) and xml_msg_config != '-1' union  select efet_msg_config || '' from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + " ))) and efet_msg_config != '-1' union  select is_upload_back_file || '' from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + " ))) and is_upload_back_file != '-1' ) union SELECT FILE_FULLPATH FROM " + tableName + " where id in (select file_path from opc_base_product  where  id in ( " + productIds + "  )) union SELECT FILE_FULLPATH FROM " + tableName + " where id in ( select file_path from opc_product  where  id in( SELECT product_id FROM opc_product_api WHERE api_id IN (SELECT id FROM opc_api WHERE backservice_id IN (" + productIds + "  ))))");
                    }else {
                        rows = jdbcTemplate.queryForList("SELECT * FROM " + tableName + " where id in ( select concat(msg_config_id,'') from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + " ))) and msg_config_id != '-1' union select concat(bus_id,'') from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + " ))) and bus_id != '-1' union  select concat(xml_msg_id,'') from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + " ))) and xml_msg_id != '-1' union  select concat(json_msg_config,'') from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + " ))) and json_msg_config != '-1' union  select concat(sep_msg_config,'') from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + " ))) and sep_msg_config != '-1' union  select concat(fixed_msg_config,'') from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + " ))) and fixed_msg_config != '-1'  union  select concat(kv_msg_config,'') from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + " ))) and kv_msg_config != '-1' union  select concat(xml_msg_config,'') from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + " ))) and xml_msg_config != '-1' union  select concat(efet_msg_config,'') from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + " ))) and efet_msg_config != '-1' union  select concat(is_upload_back_file,'') from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + " ))) and is_upload_back_file != '-1' ) union SELECT * FROM " + tableName + " where id in (select file_path from opc_base_product  where  id in ( " + productIds + "  )) union SELECT * FROM " + tableName + " where id in ( select file_path from opc_product  where  id in( SELECT product_id FROM opc_product_api WHERE api_id IN (SELECT id FROM opc_api WHERE backservice_id IN (" + productIds + "  ))))");
                        FileMap = jdbcTemplate.queryForList("SELECT FILE_FULLPATH FROM " + tableName + " where id in ( select concat(msg_config_id,'') from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + " ))) and msg_config_id != '-1' union select concat(bus_id,'') from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + " ))) and bus_id != '-1' union  select concat(xml_msg_id,'') from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + " ))) and xml_msg_id != '-1' union  select concat(json_msg_config,'') from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + " ))) and json_msg_config != '-1' union  select concat(sep_msg_config,'') from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + " ))) and sep_msg_config != '-1' union  select concat(fixed_msg_config,'') from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + " ))) and fixed_msg_config != '-1'  union  select concat(kv_msg_config,'') from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + " ))) and kv_msg_config != '-1' union  select concat(xml_msg_config,'') from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + " ))) and xml_msg_config != '-1' union  select concat(efet_msg_config,'') from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + " ))) and efet_msg_config != '-1' union  select concat(is_upload_back_file,'') from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + " ))) and is_upload_back_file != '-1' ) union SELECT FILE_FULLPATH FROM " + tableName + " where id in (select file_path from id  where  product_ename in ( " + productIds + "  )) union SELECT FILE_FULLPATH FROM " + tableName + " where id in ( select file_path from opc_product  where  id in(SELECT product_id FROM opc_product_api WHERE api_id IN (SELECT id FROM opc_api WHERE backservice_id IN ( " + productIds + "  ))))");
                    }
                    rows.addAll(jdbcTemplate.queryForList("SELECT * FROM " + tableName + " where id in ( "+ fileIds +" )"));
                    FileMap.addAll(jdbcTemplate.queryForList("SELECT FILE_FULLPATH FROM " + tableName + " where id in ( "+ fileIds +" )"));


                    for(Map<String, Object> dowFile : FileMap){
                        String file_fullpath = (String)dowFile.get("FILE_FULLPATH");
                        fileList.add(new File(file_fullpath));
                    }

                    break;
                case "opc_policy_item_metadata":
                    rows = jdbcTemplate.queryForList("SELECT * FROM " + tableName + " where API_ID in ( select id from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(select id from opc_base_product  where  product_ename in (" + productIds + "))))) for update");
                    break;
                case "opc_policy":
                    rows = jdbcTemplate.queryForList("SELECT * FROM " + tableName + " where API_ID in ( select id from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where backservice_id in(" + productIds + ")))) for update");
                    FileMap = jdbcTemplate.queryForList("SELECT FILE_NAME, FILE_URL FROM " + tableName + " where API_ID in ( select id from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where backservice_id in(" + productIds + ")))) for update");

                    for(Map<String, Object> dowFile : FileMap){
                        String file_fullpath = (String)dowFile.get("FILE_URL") + (String)dowFile.get("FILE_NAME");
                        fileList.add(new File(file_fullpath));
                    }
                    break;
                case "opc_policy_tag":
                    rows = jdbcTemplate.queryForList("SELECT * FROM " + tableName + " where policy_id in ( select id from opc_policy where API_ID in ( select id from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + "))))) for update");
                    break;
                case "OPC_POLICY_ITEM":
                    rows = jdbcTemplate.queryForList("SELECT * FROM " + tableName + " where policy_tag_id in ( select id from opc_policy_tag where policy_id in ( select id from opc_policy where API_ID in ( select id from opc_api where ATOMIC_SERVICE_ID in(select  id  from opc_backservice where id in (select ATOMIC_SERVICE_ID from opc_api  where BACKSERVICE_ID in(" + productIds + ")))))) for update");
                    break;
                default:
                    break;
            }
            jsonObject.put("fileList", fileList);
            log.info(tableName + "表需要导出的数据为：{}", rows);
            sql = new StringBuilder();
            for(Map<String,Object> map: rows){
                StringBuilder column = new StringBuilder("(");
                for (TableDataMapper.TableData tableData : rs){
                    if(map.get(tableData.getColumn_name()) != null){
                        if("DATE".equals(tableData.getData_type().toUpperCase())){
                            column.append("TIMESTAMP'" + map.get(tableData.getColumn_name()) + "',");
                        }else if("NCLOB".equals(tableData.getData_type().toUpperCase())){
                            column.append("TO_CLOB('" + map.get(tableData.getColumn_name()) + "'),");
                        }else if("NUMBER".equals(tableData.getData_type().toUpperCase())){
                            column.append(map.get(tableData.getColumn_name()) + ",");
                        }else{
                            column.append("'" + map.get(tableData.getColumn_name()) + "',");
                        }
                    }else{
                        column.append(null + ",");
                    }
                }

                column.replace(column.length() - 1, column.length(), ")");

                sql.append(topHalf.toString() + " VALUES " + column.toString() + ";");
                sql.append("\r\n");
            }

        } catch (SQLException e) {
            log.error("异常信息：{}", e);
        }

        log.info(tableName + "生成的新增sql:{}", sql);
        jsonObject.put("sql", sql.toString());
        return jsonObject;
    }

    /**
     * //根据数据库生成字段 例如 private Object a;
     * @param tableName 表名称
     * @param apiIds apiIds
     * @param downloadNumType 导出类型 0-全部 1-非门户
     * @return 成员变量拼接后的字符串
     * @throws SQLException
     */
    public <T> JSONObject getInsertSqlbyApiId(String tableName, String apiIds,String downloadNumType) {
        StringBuilder sql = null;
        JSONObject jsonObject = new JSONObject();
        try(Connection connection = jdbcTemplate.getDataSource().getConnection();){
            DatabaseMetaData data = connection.getMetaData();
            String driverName = data.getDriverName();
            StringBuffer topHalf = new StringBuffer("INSERT INTO " + tableName.toUpperCase() + " (");
            List<TableDataMapper.TableData> rs = null;
            if(driverName.contains("Oracle")){
                //oracle数据库
                rs = jdbcTemplate.query("select DISTINCT COLUMN_NAME,DATA_TYPE from USER_TAB_COLUMNS where table_name='" + tableName.toUpperCase() + "'", new TableDataMapper());

                for (TableDataMapper.TableData tableData : rs){
                    if("DESCRIBE".equals(tableData.getColumn_name().toUpperCase())){
                        topHalf.append('"' + tableData.getColumn_name()+ '"' + ",");
                    }else{
                        topHalf.append(tableData.getColumn_name()+ ",");
                    }
                }
            } else{
                //mysql数据库
                String url = data.getURL();
                String[] split = url.split(":");
                String[] portSplit = split[3].split("/");
                String[] databaseSplit = portSplit[1].split("\\?");
                String dbName = databaseSplit[0];

                rs = jdbcTemplate.query("select column_name,data_type  from information_schema.columns where table_schema='" + dbName + "' and table_name='" + tableName.toLowerCase() + "'", new TableDataMapper());

                for (TableDataMapper.TableData tableData : rs){
                    topHalf.append( "`" + tableData.getColumn_name()+ "`,");
                }
            }

            topHalf = new StringBuffer(StrUtil.removeSuffix(topHalf.toString(), ","));
            topHalf.append(") ");
            log.info("字段：{}", topHalf);
            List<Map<String,Object>> rows = new ArrayList<>();
            List<Map<String,Object>> FileMap = new ArrayList<>();
            ArrayList fileList = new ArrayList();
            switch (tableName) {
                case "opc_exp_seq":
//                    rows = jdbcTemplate.queryForList("SELECT * FROM " + tableName + " where EXP_SEQ in ( select EXP_SEQ  from opc_base_product  where  id in   (select BACKSERVICE_ID from opc_api where id in ( "+apiIds+" )))");
                    break;
                case "opc_sys":
                    rows = jdbcTemplate.queryForList("SELECT * FROM " + tableName + " where id in (select opc_sys_id from opc_base_product  where  id in ( select backservice_id from opc_api where id in(" + apiIds + ") ))");
                    break;
                case "opc_base_product":
                    rows = jdbcTemplate.queryForList("SELECT * FROM " + tableName + " where id in ( select backservice_id from opc_api where id in ( " + apiIds + " ))");
                    break;
//                    4
                case "opc_backservice":
                    rows = jdbcTemplate.queryForList("SELECT * FROM " + tableName + " where id in ( select atomic_service_id from opc_api  where id in (" + apiIds + " ) )");
                    break;
//                    5
                case "opc_api":
                    rows = jdbcTemplate.queryForList("SELECT * FROM " + tableName + " where id in (" + apiIds + " )");
                    if ("1".equals(downloadNumType)) {
                        for (Map<String, Object> map : rows) {
                            map.put("STATUS", 9);
                        }
                    }
                    break;
                case "opc_gateway_route":
                    rows = jdbcTemplate.queryForList("SELECT * FROM " + tableName + " a where  a.opc_api_id in(" + apiIds + " )");
                    break;
                case "opc_product":
                    rows.addAll(jdbcTemplate.queryForList("SELECT * FROM " + tableName + " where id in  (select product_id from opc_product_api where api_id in (" + apiIds + " ))"));
                    if ("1".equals(downloadNumType)) {
                        for (Map<String, Object> map : rows) {
                            map.put("STATUS", 9);
                        }
                    }
                    break;
                case "opc_product_api":
                    rows = jdbcTemplate.queryForList("SELECT * FROM " + tableName + " where api_id in (" + apiIds + " )");
                    if ("1".equals(downloadNumType)) {
                        for (Map<String, Object> map : rows) {
                            map.put("STATUS", 9);
                        }
                    }
                    break;
                case "opc_app":
                    if ("0".equals(downloadNumType)) {
                        rows = jdbcTemplate.queryForList("SELECT * FROM " + tableName + " where id in( select opc_app_id from opc_app_api where opc_api_id in (" + apiIds + " ) )");
                    }
                    break;
                case "opc_app_api":
                    if ("0".equals(downloadNumType)) {
                        rows = jdbcTemplate.queryForList("SELECT * FROM " + tableName + " where opc_api_id in (" + apiIds + " )");
                    }
                    break;
                case "opc_issue_api":
                    if ("0".equals(downloadNumType)) {
                        rows = jdbcTemplate.queryForList("SELECT * FROM " + tableName + " where api_id in (" + apiIds + " )");
                    }
                    break;
                case "opc_externalcall":
                    rows = jdbcTemplate.queryForList("SELECT * FROM " + tableName + " where backservice_id in (select atomic_service_id from opc_api  where id in (" + apiIds + " ) )");
                    break;
                case "opc_test_apply":
                    if ("0".equals(downloadNumType)) {
                        rows = jdbcTemplate.queryForList("SELECT * FROM " + tableName + " where app_id in ( select id from opc_app where id in( select opc_app_id from opc_app_api where opc_api_id in (" + apiIds + " ) ) )");
                    }
                    break;
                case "opc_produce_apply":
                    if ("0".equals(downloadNumType)) {
                        rows = jdbcTemplate.queryForList("SELECT * FROM " + tableName + " WHERE app_id in ( select id from opc_app where id in( select opc_app_id from opc_app_api where opc_api_id in (" + apiIds + " ) ) )");
                    }
                    break;
                case "opc_online_apply":
                    if ("0".equals(downloadNumType)) {
                        rows = jdbcTemplate.queryForList("SELECT * FROM " + tableName + " WHERE app_id in( select id from opc_app where id in( select opc_app_id from opc_app_api where opc_api_id in (" + apiIds + " ) ) )");
                    }
                    break;
                case "opc_orga":
                    if ("0".equals(downloadNumType)) {
                        rows = jdbcTemplate.queryForList("SELECT * FROM " + tableName + " where id in ( select opc_orga_id from opc_app where id in( select opc_app_id from opc_app_api where opc_api_id in (" + apiIds + " ) ) ) ");
                    }
                    break;
                case "opc_user":
                    if ("0".equals(downloadNumType)) {
                        rows = jdbcTemplate.queryForList("SELECT * FROM " + tableName + " where id in ( select opc_user_id from opc_orga where id in ( select opc_orga_id from opc_app where id in( select opc_app_id from opc_app_api where opc_api_id in (" + apiIds + " ) ) ) )");
                    }
                    break;
                case "opc_plupload":
                    List<Map<String,Object>> ids = jdbcTemplate.queryForList("select file_ids from opc_api where id in   (" + apiIds + " )");
                    String fileIds = "";
                    for (int j = 0; j < ids.size(); j++) {
                        Map<String, Object> map = ids.get(j);
                        if(StringUtils.isNotEmpty((String) map.get("FILE_IDS"))){
                            String[] file_ids = ((String) map.get("FILE_IDS")).split(",");
                            if (file_ids.length > 0) {
                                for (int i = 0; i < file_ids.length; i++) {
                                    if (i == (file_ids.length - 1)) {
                                        fileIds = fileIds + "'" + file_ids[i] + "'";
                                    } else {
                                        fileIds = fileIds + "'" + file_ids[i] + "',";
                                    }
                                }
                            } else {
                                fileIds = fileIds + "'" + (String) map.get("FILE_IDS") + "'";
                            }
                            if (j != (ids.size() - 1)){
                                if (fileIds.lastIndexOf(',') != fileIds.length()){
                                    fileIds = fileIds + ",";
                                }
                            }
                        }
                    }
                  if (fileIds == ",") {
                        fileIds = "";
                    }
                    if (fileIds != "") {
                        if (fileIds.lastIndexOf(',') == fileIds.length() - 1) {
                            fileIds = fileIds.substring(0, fileIds.length() - 1);
                        }
                    }else {
                        fileIds = "''";
                    }
                   rows = jdbcTemplate.queryForList(" SELECT * FROM opc_plupload where id in ( select concat(msg_config_id,'') from opc_api where id in ( "+ apiIds +" ) and msg_config_id != '-1' union select concat(bus_id,'') from opc_api where id in ( "+ apiIds +" )  and bus_id != '-1' union  select concat(xml_msg_id,'') from opc_api where id in ( "+ apiIds +" ) and xml_msg_id != '-1' union  select concat(json_msg_config,'') from opc_api where id in ( "+ apiIds +" ) and json_msg_config != '-1' union  select concat(sep_msg_config,'') from opc_api where id in ( "+ apiIds +" ) and sep_msg_config != '-1' union  select concat(fixed_msg_config,'') from opc_api where id in ( "+ apiIds +" ) and fixed_msg_config != '-1'  union  select concat(kv_msg_config,'') from opc_api where id in ( "+ apiIds +" ) and kv_msg_config != '-1' union  select concat(xml_msg_config,'') from opc_api where id in ( "+ apiIds +" ) and xml_msg_config != '-1' union  select concat(efet_msg_config,'') from opc_api where id in ( "+ apiIds +" ) and efet_msg_config != '-1' union  select concat(is_upload_back_file,'') from opc_api where id in ( "+ apiIds +" ) and is_upload_back_file != '-1' ) union SELECT * FROM opc_plupload where id in ( select file_path from opc_product  where  id in   ( select BACKSERVICE_ID from opc_api where id in ( "+ apiIds +" ) )) union SELECT *  FROM opc_plupload WHERE id in (select file_path from opc_base_product where id in ( select backservice_id from opc_api where id in ( "+ apiIds +" ) ))");
                    rows.addAll(jdbcTemplate.queryForList("SELECT * FROM " + tableName + " where id in ( "+ fileIds +" )"));
                    FileMap = jdbcTemplate.queryForList(" SELECT FILE_FULLPATH FROM opc_plupload where id in ( select concat(msg_config_id,'') from opc_api where id in ( "+ apiIds +" ) and msg_config_id != '-1' union select concat(bus_id,'') from opc_api where id in ( "+ apiIds +" )  and bus_id != '-1' union  select concat(xml_msg_id,'') from opc_api where id in ( "+ apiIds +" ) and xml_msg_id != '-1' union  select concat(json_msg_config,'') from opc_api where id in ( "+ apiIds +" ) and json_msg_config != '-1' union  select concat(sep_msg_config,'') from opc_api where id in ( "+ apiIds +" ) and sep_msg_config != '-1' union  select concat(fixed_msg_config,'') from opc_api where id in ( "+ apiIds +" ) and fixed_msg_config != '-1'  union  select concat(kv_msg_config,'') from opc_api where id in ( "+ apiIds +" ) and kv_msg_config != '-1' union  select concat(xml_msg_config,'') from opc_api where id in ( "+ apiIds +" ) and xml_msg_config != '-1' union  select concat(efet_msg_config,'') from opc_api where id in ( "+ apiIds +" ) and efet_msg_config != '-1' union  select concat(is_upload_back_file,'') from opc_api where id in ( "+ apiIds +" ) and is_upload_back_file != '-1' ) union SELECT FILE_FULLPATH FROM opc_plupload where id in ( select file_path from opc_product  where  id in   ( select BACKSERVICE_ID from opc_api where id in ( "+ apiIds +" ) )) union SELECT FILE_FULLPATH  FROM opc_plupload WHERE id in (select file_path from opc_base_product where id in ( select backservice_id from opc_api where id in ( "+ apiIds +" ) ))");
                    FileMap.addAll(jdbcTemplate.queryForList("SELECT FILE_FULLPATH FROM " + tableName + " where id in ( "+ fileIds +" )"));

                    for(Map<String, Object> dowFile : FileMap){
                        String file_fullpath = (String)dowFile.get("FILE_FULLPATH");
                        fileList.add(new File(file_fullpath));
                    }
                    break;
                case "opc_policy_item_metadata":
                    rows = jdbcTemplate.queryForList("SELECT * FROM " + tableName + " where api_id in  (" + apiIds + ")");
                    break;
                case "opc_policy":
                    rows = jdbcTemplate.queryForList("SELECT * FROM " + tableName + " where api_id in (" + apiIds + ")");

                    FileMap = jdbcTemplate.queryForList("SELECT FILE_NAME, FILE_URL FROM " + tableName + " where api_id in (" + apiIds + ")");

                    for(Map<String, Object> dowFile : FileMap){
                        String file_fullpath = (String)dowFile.get("FILE_URL") + (String)dowFile.get("FILE_NAME");
                        fileList.add(new File(file_fullpath));
                    }

                    break;
                case "opc_policy_tag":
                    rows = jdbcTemplate.queryForList("SELECT * FROM " + tableName + " where policy_id in ( select id from opc_policy where API_ID in (" + apiIds + "))");
                    break;
                case "OPC_POLICY_ITEM":
                    rows = jdbcTemplate.queryForList("SELECT * FROM " + tableName + " where policy_tag_id in ( select id from opc_policy_tag where policy_id in ( select id from opc_policy where API_ID in (" + apiIds + ")))");
                    break;
                default:
                    break;
            }
            jsonObject.put("fileList", fileList);
            log.info(tableName + "表需要导出的数据为：{}", rows);
            sql = new StringBuilder();
            for(Map<String,Object> map: rows){
                StringBuilder column = new StringBuilder("(");
                for (TableDataMapper.TableData tableData : rs){
                    if(map.get(tableData.getColumn_name()) != null){
                        if("DATE".equals(tableData.getData_type().toUpperCase())){
                            column.append("TIMESTAMP'" + map.get(tableData.getColumn_name()) + "',");
                        }else if("NCLOB".equals(tableData.getData_type().toUpperCase())){
                            column.append("TO_CLOB('" + map.get(tableData.getColumn_name()) + "'),");
                        }else if("NUMBER".equals(tableData.getData_type().toUpperCase())){
                            column.append(map.get(tableData.getColumn_name()) + ",");
                        }else{
                            column.append("'" + map.get(tableData.getColumn_name()) + "',");
                        }
                    }else{
                        column.append(null + ",");
                    }
                }

                column.replace(column.length() - 1, column.length(), ")");

                sql.append(topHalf.toString() + " VALUES " + column.toString() + ";");
                sql.append("\r\n");
            }
        } catch (SQLException e) {
            log.error("异常信息：{}", e);
        }
        log.info(tableName + "生成的新增sql:{}", sql);
        jsonObject.put("sql", sql.toString());
        return jsonObject;
    }

    /**
     * 获取bean里面的属性
     * @param str
     * @return
     */
    public static String lineToHump(String str) {
        str = str.toLowerCase();
        Matcher matcher = linePattern.matcher(str);
        StringBuffer sb = new StringBuffer();
        while (matcher.find()) {
            matcher.appendReplacement(sb, matcher.group(1).toUpperCase());
        }
        matcher.appendTail(sb);
        return sb.toString();
    }
    /**
     * 时间转换
     * @param dateStr
     * @return
     * @throws ParseException
     */
    public static String CSTtoDate(String dateStr) throws ParseException {
        SimpleDateFormat sdf = new SimpleDateFormat("EEE MMM dd HH:mm:ss zzz yyyy", Locale.US);
        Date date = (Date) sdf.parse(dateStr);
        String formatStr = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(date);
        return formatStr;
    }

    private static String insertObject(List<?> objectList) throws IllegalAccessException {
        /**
         * 比如：
         * SQL 语句：insert into person(name, age, info) values ('...', '...', '...');
         * 其中 values 改为占位符：
         * SQL 语句：insert into person(name, age, info) values (?, ?, ?);
         * 再获取所有的数据值 Object 数组
         * [zhengbin, 21, 要毕业了]
         */
        // 插入数据的列名
        StringBuilder columns = new StringBuilder("(");
        // 填充占位符的值（即对象中不为null的字段的值）
        List<Object> valuesList = new ArrayList<Object>();
        // 如果为空则不执行
        if (CollectionUtil.isEmpty(objectList)) {
            return null;
        }
        // 通过 List 中的第一个 Object，确定插入对象的字段
        Object object = objectList.get(0);
        Class clazz = object.getClass();
        Field[] fields = clazz.getDeclaredFields();
        // 获得父类的属性
        Field[] superFields = clazz.getSuperclass().getDeclaredFields();
        // 自己的和父类的属性相加
        Field[] allFields = addFields(superFields, fields);
        for (Field field : allFields) {
            if("serialVersionUID".equals(field.getName())){
                continue;
            }
            field.setAccessible(true);
            columns.append(field.getName()).append(", ");
        }
        columns.replace(columns.lastIndexOf(", "), columns.length(), ")");

        // 获取所有的值
        StringBuilder val = new StringBuilder();
        for (int i = 0; i < objectList.size(); i++) {
            StringBuilder column = new StringBuilder("(");
            for (Field field : allFields) {
                if("serialVersionUID".equals(field.getName())){
                    continue;
                }
                field.setAccessible(true);
                column.append(field.get(objectList.get(i)) + ", ");
            }
            column.replace(column.lastIndexOf(", "), column.length(), ")");
            val.append(column.toString()).append(", ");
        }


        val.replace(val.lastIndexOf(", "), val.length(), "");

        // 生成最终 INSERTSQL结果
        String insertSql = "INSERT INTO " + object.getClass().getSimpleName().toLowerCase() + " " + columns + " VALUES " + val.toString();
        System.out.println(insertSql);

        return insertSql;
    }


    /**
     * 相加f1和f2的Field
     *
     * @param f1
     * @param f2
     * @return
     */
    private static Field[] addFields(Field[] f1, Field[] f2) {
        List<Field> fields = new ArrayList<>();
        for (Field field : f1) {
            fields.add(field);
        }
        for (Field field : f2) {
            fields.add(field);
        }
        return fields.toArray(new Field[f1.length + f2.length]);
    }


    /*
     * @Description 查询需要导出的表名
     * @Author xiafayu
     * @Date 2020/12/23 17:15
     * @param null
     * @return
     * @throws
     **/
    public List<String>  getTableNameByExpSeq(){
        String sql = "SELECT a.table_name,b.comments FROM USER_TAB_COLUMNS a left join user_tab_comments b on a.TABLE_NAME=b.TABLE_NAME WHERE COLUMN_NAME='EXP_SEQ' AND a.TABLE_NAME IN (SELECT TABLE_NAME FROM tabs)";
        List<ExpSeqEntity> list = jdbcTemplate.query(sql, new TableNameMapper());
        List<String>  tabList = new ArrayList<>();
        for(ExpSeqEntity tal : list){
            tabList.add(tal.getTableEname());
        }
        return tabList;
    }

    /*
     * @Description 根据表名查询新增数据数量
     * @Author xiafayu
     * @Date 2020/12/24 11:40
     * @param tabList
     * @return com.dhcc.bpm.common.json.JSONObject
     * @throws
     **/
    public JSONObject queryData(List<String> tabList){
        JSONObject json = new JSONObject();
        ValueOperations<String, String> opsForValue = stringRedisTemplate.opsForValue();
        String sql = null;
        for(String name : tabList){
            sql = "SELECT count(*)  FROM " + name.toUpperCase() + " WHERE exp_seq='"+ opsForValue.get(EXP_SQE) +"'";
            Integer count = jdbcTemplate.queryForObject(sql, Integer.class);
            json.put(name.toLowerCase(), Long.valueOf(count));
        }
        return json;
    }
    /*
     * @Description 根据表名查询新增数据数量
     * @Author xiafayu
     * @Date 2020/12/24 11:40
     * @param tabList
     * @return com.dhcc.bpm.common.json.JSONObject
     * @throws
     **/
    public Integer queryDataByName( String tabName, String expSeq){
        if(tabName.indexOf("copy") > 1){
            return 0;
        }
        String sql = "SELECT count(*)  FROM " + tabName.toUpperCase() + " WHERE exp_seq='"+ expSeq +"'";
        return jdbcTemplate.queryForObject(sql, Integer.class);
    }

    /*
     * @Description 查询需要导出的表名及需要导出的数据条数
     * @Author xiafayu
     * @Date 2020/12/23 17:15
     * @param null
     * @return
     * @throws
     **/
    public List<ExpSeqEntity>  getTableDataByExpSeq(){
        List<ExpSeqEntity> json = new ArrayList<>();
//        String sql = "SELECT TABLE_NAME  FROM USER_TAB_COLUMNS WHERE COLUMN_NAME='EXP_SEQ'";
        String sql = "SELECT a.TABLE_NAME,b.COMMENTS FROM USER_TAB_COLUMNS a left join user_tab_comments b on a.TABLE_NAME=b.TABLE_NAME WHERE COLUMN_NAME='EXP_SEQ' AND a.TABLE_NAME IN (SELECT TABLE_NAME FROM tabs)";
        List<ExpSeqEntity> list = jdbcTemplate.query(sql, new TableNameMapper());
        ValueOperations<String, String> opsForValue = stringRedisTemplate.opsForValue();
        for(ExpSeqEntity tal : list){
            if(tal.getTableEname().indexOf("copy") > 1){
                continue;
            }
            sql = "SELECT count(*)  FROM " + tal.getTableEname().toUpperCase() + " WHERE exp_seq='"+ opsForValue.get(EXP_SQE) +"'";
            Integer count = jdbcTemplate.queryForObject(sql, Integer.class);
            tal.setNum(count);
            json.add(tal);
        }
        return json;
    }

}
